/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.model;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.db.SqlManager;
import com.exception.DataException;
import com.javabeans.TipoGestion;
/**
 * @author Administrador
 */
public class AdminTipoGestion implements TipoGestionManager{
    private Connection c;

    public AdminTipoGestion(Connection c){
        this.c = c;
    }
    
    public int getIdTipoGestion() throws DataException {
        int res = 0;
        ResultSet rs = null;
        PreparedStatement preSt = null;
        String query = "SELECT IFNULL(max(IDTIPO_GESTION)+1,1) AS ID FROM TIPO_GESTION";
        try{
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            rs.next();
            res = rs.getInt("ID");
        }catch(SQLException e){
            throw new DataException(" AdminTipoGestion -> getIdTipoGestion() "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return res;
    }
    
    @Override
    public void saveTipoGestion(TipoGestion tg) throws DataException {
        PreparedStatement preSt = null;
        try{
            String query = "INSERT INTO TIPO_GESTION VALUES("+tg.getIdTipoGestion();
            query += ", '"+tg.getNombre().toUpperCase().trim();
            query += "', "+tg.getIdTipoGestion();
            query += ", "+tg.getIdTipoGestion();
            query += ","+tg.isActivo()+")";            
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException ex){
            throw new DataException(" AdminTipoGestion saveTipoGestion(Categoria ca) "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
    @Override
    public void updateTipoGestion(TipoGestion tg) throws DataException {
        PreparedStatement preSt = null;
        try{
            String query = "UPDATE TIPO_GESTION SET NOMBRE = '"+tg.getNombre().toUpperCase().trim();
            query+= "' WHERE IDTIPO_GESTION ="+tg.getIdTipoGestion();
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException ex){
            throw new DataException(" AdminTipoGestion -> updateTipoGestion(TipoGestion tg) e = "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
    public void darBaja(int idTipoGestion) throws DataException{
        PreparedStatement preSt = null;
        try{
            String query = "UPDATE TIPO_GESTION SET ACTIVO = 0 WHERE IDTIPO_GESTION = "+idTipoGestion;
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException ex){
            throw new DataException(" AdminTipoGestion -> darBaja(int idTipoGestion) e = "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
    public void darAlta(int idTipoGestion) throws DataException{
        PreparedStatement preSt = null;
        try{
            String query = "UPDATE TIPO_GESTION SET ACTIVO = 1 WHERE IDTIPO_GESTION = "+idTipoGestion;
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException ex){
            throw new DataException(" AdminTipoGestion -> darAlta(int idTipoGestion) e = "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
    public TipoGestion getTipoGestion(int idTipoGestion) throws DataException{
        TipoGestion tg = new TipoGestion();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT NOMBRE, ACTIVO";
            query += " FROM TIPO_GESTION WHERE IDTIPO_GESTION = "+idTipoGestion;            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            if(rs.next()){
                tg.setIdTipoGestion(idTipoGestion);
                tg.setNombre(rs.getString("NOMBRE"));
                tg.setActivo(rs.getBoolean("ACTIVO"));
            }
        }catch(SQLException e){
            throw new DataException(" AdminTipoGestion -> getTipoGestion(int idTipoGestion) e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return tg;
    }
    
    public List<TipoGestion> getTipoGestiones() throws DataException{
        List<TipoGestion> tipoGestiones = new ArrayList<TipoGestion>();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT IDTIPO_GESTION, NOMBRE, IDFACULTAD";
            query += " FROM TIPO_GESTION WHERE ACTIVO = 1";
            query += " ORDER BY ORDEN";            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            while(rs.next()){
                TipoGestion tg = new TipoGestion();
                tg.setIdTipoGestion(rs.getInt("IDTIPO_GESTION"));
                tg.setNombre(rs.getString("NOMBRE"));
                tg.setIdFacultad(rs.getInt("IDFACULTAD"));
                tipoGestiones.add(tg);
            }
        }catch(SQLException e){
            throw new DataException(" AdminTipoGestion -> getTipoGestiones() e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return tipoGestiones;
    }
    
    public List<TipoGestion> getTipoGestionesInactivos() throws DataException{
        List<TipoGestion> tipoGestiones = new ArrayList<TipoGestion>();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT IDTIPO_GESTION, NOMBRE, IDFACULTAD";
            query += " FROM TIPO_GESTION WHERE ACTIVO = 0";
            query += " ORDER BY NOMBRE";            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            while(rs.next()){
                TipoGestion tg = new TipoGestion();
                tg.setIdTipoGestion(rs.getInt("IDTIPO_GESTION"));
                tg.setNombre(rs.getString("NOMBRE"));
                tg.setIdFacultad(rs.getInt("IDFACULTAD"));
                tipoGestiones.add(tg);
            }
        }catch(SQLException e){
            throw new DataException(" AdminTipoGestion -> getTipoGestionesInactivos() e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return tipoGestiones;
    }
    
    public boolean isUseForDelete(int idTipoGestion) throws DataException{
        boolean res = false;
        ResultSet rs = null;
        PreparedStatement preSt = null;
        String query = "SELECT IDGESTION";
        query += " FROM GESTION";
        query += " WHERE IDTIPO_GESTION="+idTipoGestion;
        try{
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            if(rs.next())
                res = true;
        }catch(SQLException e){
            throw new DataException(" AdminTipoGestion -> isUseForDelete() "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return res;
    }
    
    public void deleteTipoGestion(int idTipoGestion) throws DataException {
        PreparedStatement preSt = null;
        String query = "DELETE FROM TIPO_GESTION WHERE IDTIPO_GESTION="+idTipoGestion;
        try{
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException e){
            throw new DataException(" AdminTipoGestion -> deleteTipoGestion(int idTipoGestion) e = "+e.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
}
